import mysql.connector
import configparser
from model.employee import Employee_Args


class Database(object):
    # similar as the static method in java
    @classmethod
    def create_connection(cls):
        cp = configparser.ConfigParser()
        cp.read('model/config.ini')
        USERNAME = cp.get('DB_SECTION', 'username')
        PASSWORD = cp.get('DB_SECTION', 'password')
        HOST = cp.get('DB_SECTION', 'host')
        DATABASE = cp.get('DB_SECTION', 'database')
        connection = mysql.connector.connect(username=USERNAME, password=PASSWORD, host=HOST, database=DATABASE)
        return connection

    @classmethod
    def save(cls, emp):
        sql = 'insert into employee(code, name) values (%s, %s)'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql, (emp.code, emp.name))
        conn.commit()
        cursor.close()
        conn.close()

    @classmethod
    def query(cls):
        sql = 'select * from employee'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql)
        result_set = cursor.fetchall()
        cursor.close()
        conn.close()
        return result_set

    @classmethod
    def save(cls, emp):
        sql = 'insert into employee(code, name) values (%s, %s)'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql, (emp.code, emp.name))
        conn.commit()
        cursor.close()
        conn.close()

    @classmethod
    def update(cls, emp):
        sql = 'update employee set code=%s, name=%s where id=%s'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql, (emp.code, emp.name, emp.id))
        conn.commit()
        cursor.close()
        conn.close()

    @classmethod
    def query_by_id(cls, id):
        sql = 'select * from employee where id = %s'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql, [id])
        result_set = cursor.fetchall()
        cursor.close()
        conn.close()
        if len(result_set) == 0:
            return None
        else:
            return result_set[0]

    @classmethod
    def delete(cls, id):
        sql = 'delete from employee where id = %s'
        conn = Database.create_connection()
        cursor = conn.cursor()
        cursor.execute(sql, [id])
        conn.commit()
        cursor.close()
        conn.close()


if __name__ == '__main__':
    # emp = Employee_Args('E01', 'Jacky')
    # Database.save(emp)
    print(Database.query())
